﻿using System;
using System.Collections.Generic;
using System.Data;
using System.Data.SqlClient;
using System.Linq;
using System.Web;

/// <summary>
/// Summary description for QuanLyDAL
/// </summary>
public class QuanLyDAL
{
    public static object IsNull(object checkValue, object replaceValue)
    {
        if (checkValue.Equals(System.DBNull.Value) || checkValue == null)
            return replaceValue;
        return checkValue;
    }

    public static List<QuanLyINFO> listAll()
    {
        string sql = "select * from QuanLy";
        SqlDataReader dr = SQLHelper.ExecuteReader(Conn.ConnectionString, CommandType.Text, sql, null);
        return SQLHelper.SetListObjects<QuanLyINFO>(dr);
    }

    public static QuanLyINFO GetInfo(int MaQL)
    {
        string sql = "select * from QuanLy where MaQL=" + MaQL;
        SqlDataReader dr = SQLHelper.ExecuteReader(Conn.ConnectionString, CommandType.Text, sql, null);
        return SQLHelper.SetObject<QuanLyINFO>(dr);
    }

    public static QuanLyINFO GetInfo(string Email)
    {
        string sql = "select * from QuanLy where Email='" + Email + "'";
        SqlDataReader dr = SQLHelper.ExecuteReader(Conn.ConnectionString, CommandType.Text, sql, null);
        return SQLHelper.SetObject<QuanLyINFO>(dr);
    }

    public static List<int> QL_CN(int MaQL)
    {
        string sql = "select * from CHUCNANG_QUANLY where MaQL=" + MaQL;
        SqlDataReader dr = SQLHelper.ExecuteReader(Conn.ConnectionString, CommandType.Text, sql, null);
        List<int> list = new List<int>();
        while (dr.Read())
        {
            int Ma = Convert.ToInt32(IsNull(dr["MaChucNang"], 0));
            list.Add(Ma);
        }
        dr.Close();
        return list;
    }

    public static int Insert(QuanLyINFO obj)
    {
        obj.MaQL = GetMa();
        obj.MaQL = obj.MaQL + 1;
        string sql = "INSERT INTO QuanLy (MaQL,HoTen,Email,MatKhau,SDT,TrangThai) VALUES (" + obj.MaQL + ",N'" + obj.HoTen + "','" + obj.Email + " ','" + obj.MatKhau + "',N'" + obj.SDT + "'," + obj.TrangThai + ")";
        return SQLHelper.ExecuteNonQuery(Conn.ConnectionString, CommandType.Text, sql, null);
    }

    public static int Update(QuanLyINFO obj)
    {
        string sql = "Update QuanLy set HoTen=N'" + obj.HoTen + "', Email='" + obj.Email + "', SDT='" + obj.SDT + "',TrangThai=" + obj.TrangThai + " Where MaQL=" + obj.MaQL + "";
        return SQLHelper.ExecuteNonQuery(Conn.ConnectionString, CommandType.Text, sql, null);
    }

    public static int Delete(int MaQL)
    {
        string sql = "Delete from QuanLy where MaQL =" + MaQL + "";
        return SQLHelper.ExecuteNonQuery(Conn.ConnectionString, CommandType.Text, sql, null);
    }

    public static int GetMa()
    {
        string sql = "SELECT ISNULL(MAX(MaQL),0) FROM QuanLy";
        SqlDataReader dr = SQLHelper.ExecuteReader(Conn.ConnectionString, CommandType.Text, sql, null);
        int ma = 0;
        if (dr.Read())
        {
            ma = int.Parse(dr[0].ToString());
        }
        dr.Close();
        return ma;
    }

    public static int DoiMatKhau(int ID, string MatKhau)
    {
        string sql = "Update QuanLy set MatKhau = '"+MatKhau+"' where MaQL=" + ID;
        return SQLHelper.ExecuteNonQuery(Conn.ConnectionString, CommandType.Text, sql, null);
    }

    public static int Xoa_CN_QL(int MaQL, SqlTransaction trans)
    {
        string sql = "Delete from CHUCNANG_QUANLY where MaQL=" + MaQL;
        return SQLHelper.ExecuteNonQuery(trans, CommandType.Text, sql, null);
    }

    public static int Them_CN_QL(int MaQL, int MaCN, SqlTransaction trans) 
    {
        string sql = "INSERT INTO CHUCNANG_QUANLY(MaQL,MaChucNang) values ("+MaQL+","+MaCN+")";
        return SQLHelper.ExecuteNonQuery(trans, CommandType.Text, sql, null);
    }

    public static ChucNangINFO KiemTraQuyen(int MaQL, string path)
    {
        string sql = "SELECT CHUCNANG.* FROM CHUCNANG "; 
        sql += "INNER JOIN CHUCNANG_QUANLY ON CHUCNANG.MaChucNang = CHUCNANG_QUANLY.MaChucNang ";
        sql+= "WHERE CHUCNANG.LienKet = '"+path+"'";
        SqlDataReader dr = SQLHelper.ExecuteReader(Conn.ConnectionString, CommandType.Text, sql, null);
        return SQLHelper.SetObject<ChucNangINFO>(dr);
    }
    
    public static int Them_CN_QL(int MaQL, List<int> dsCN)
    {
        SqlConnection conns = null;
        SqlTransaction trans = null;
        int macauhoi = 0;
        try
        {
            macauhoi = GetMa();
            macauhoi++;
            conns = new SqlConnection(Conn.ConnectionString);
            if (conns.State != ConnectionState.Open)
                conns.Open();
            trans = conns.BeginTransaction();
            if (Xoa_CN_QL(MaQL, trans) <= 0)
            {
                trans.Rollback();
                conns.Close();
                return 0;
            }
            for (int i = 0; i < dsCN.Count; i++)
            {
                if (Them_CN_QL(MaQL, dsCN[i], trans) <= 0)
                {
                    trans.Rollback();
                    conns.Close();
                    return 0;
                }
            }

            trans.Commit();
            conns.Close();
            return 1;
        }
        catch (Exception)
        {
            trans.Rollback();
            conns.Close();
            return 0;
        }
    }
}